package com.starsky.project.service.impl;

import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import com.starsky.common.util.DateUtils;
import com.starsky.common.util.easypoi.ExcelUtils;
import com.starsky.project.domain.dto.BookInfoDto;
import com.starsky.project.domain.entity.EvaluationModel;
import com.starsky.project.domain.entity.UserEvaluationScore;
import com.starsky.project.domain.vo.BookInfoVo;
import com.starsky.project.mapper.BookInfoMapper;
import com.starsky.project.service.ExcelService;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.util.*;

@Service
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private BookInfoMapper bookInfoMapper;

    /**
     * 导入普通excel,从第一行开始读取，参考 bookInfo.xlsx
     *
     * @param file
     * @return
     */
    public List<BookInfoDto> importExcel(MultipartFile file) {
        //读取文件
        List<BookInfoDto> dtos = ExcelUtils.importExcel(file, 1, 1, BookInfoDto.class);
        return dtos;
    }

    /**
     * 导入合并单元格，参考template/megerColumn.xlsx
     */
    public List<EvaluationModel> importMergeCellExcel(MultipartFile file) {
        List<EvaluationModel> resList = new ArrayList<>();
        try {
            InputStream in = file.getInputStream();
            resList = ExcelUtils.importMergeCellExcel(in, 0, 0, 1, 2, EvaluationModel.class);
            System.out.println("excel解析结果： " + resList);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resList;
    }


    /**
     * 导入合并单元格，参考template/megerColumn2.xlsx
     */
    public List<EvaluationModel> importMergeCellExcel2(MultipartFile file) {
        List<EvaluationModel> resList = new ArrayList<>();
        try {
             String fileName = file.getOriginalFilename();
            Sheet sheet = ExcelUtils.getSheet(file, 0);
            final Map<String, Integer> excelColumnMap = ExcelUtils.getExcelColumnMap(sheet);
            final Integer lastRow = excelColumnMap.get("lastRow");
            ExcelUtils.getMergeCelleExcel(sheet,lastRow, UserEvaluationScore.class);

            InputStream in = file.getInputStream();
            resList = ExcelUtils.importMergeCellExcel(in, 0, 0, 1, 2, EvaluationModel.class);
            System.out.println("excel解析结果： " + resList);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return resList;
    }


    /**
     * 导出普通excel
     */
    public List<BookInfoDto> exportBookInfo(BookInfoVo param) {
        List<BookInfoDto> list = bookInfoMapper.find(param);
        return list;
    }


    /**
     * 测试多sheet导出
     */
    public List<Map<String, Object>> exportMutiSheet(BookInfoVo paramVo) {
        List<Map<String, Object>> resList = new ArrayList<>();

        for (int n = 1; n < 4; n++) {
            // 构建map
            List<BookInfoDto> dataList = bookInfoMapper.find(paramVo);
            Map<String, Object> map = new HashMap<>();
            ExportParams exportParams = new ExportParams("导出多个sheet" + n, "sheet" + n);
            map.put("title", exportParams);
            map.put("data", dataList);
            map.put("entity", BookInfoDto.class);
            resList.add(map);
        }
        return resList;
    }


    /**
     * 生成excel模板
     */
    public void exportExcelTemplate(HttpServletResponse response) {
        try {
            List<String> deptNames = new ArrayList<>();
            deptNames.add("开发一部");
            deptNames.add("开发二部");
            deptNames.add("开发三部");
            List<String> postNames = new ArrayList<>();
            postNames.add("总经理");
            postNames.add("技术经理");
            postNames.add("项目经理");
            postNames.add("普通职员");
            //模板文件starsky-excel/src/main/resource/template/userModel.xlsx
            String templateUrl = "d:/userModel.xlsx";
            XSSFWorkbook wb = (XSSFWorkbook) ExcelUtils.createWorkbook(templateUrl, ExcelType.XSSF);
            //第一行单元格添加下拉框
            XSSFSheet sheet = wb.getSheetAt(0);
            ExcelUtils.addDropDownList(wb, sheet, deptNames, 1, 100, 3);
            ExcelUtils.addDropDownList(wb, sheet, postNames, 1, 100, 4);

            String fileName = "用户导入模板.xlsx";
            ExcelUtils.writeResponse(response, wb, fileName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 通过模板文件下载excel
     */
    public void exportDynamicDataExcel(HttpServletResponse response) {
        try {
            List<Map<String, Object>> list = new ArrayList<>();
            Map<String, Object> resMap = new HashMap<>();
            resMap.put("deptName", "测试部");
            resMap.put("sumTotal", "1000");
            resMap.put("sumPrice", "1000");
            resMap.put("lowSumTotal", "1000");
            resMap.put("lowSumPrice", "1000");
            resMap.put("lowFirstTotal", "1000");
            resMap.put("lowFirstPrice", "1000");
            resMap.put("lowTwoTotal", "1000");
            resMap.put("lowTwoPrice", "1000");
            resMap.put("highSumTotal", "1000");
            resMap.put("highSumPrice", "1000");
            resMap.put("highFirstTotal", "1000");
            resMap.put("highFirstPrice", "1000");
            resMap.put("highTwoTotal", "1000");
            resMap.put("highTwoPrice", "1000");
            list.add(resMap);

            HashMap<String, Object> map = new HashMap<>();
            map.put("maplist", list);
            map.put("title", DateUtils.format(new Date(), "yyyy") + "年部门信息");

            //模版文件：starsky-excel/src/main/resource/template/bill.xlsx
            String templateUrl = "d:/bill.xlsx";
            String fileName = "部门信息-" + DateUtils.format(new Date(), DateUtils.YYYYMMDDHHMMSS);
            ExcelUtils.exportTemplate(response, templateUrl, fileName, map);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 生成excel多级级联
     */
    public void exportCascadeExcel(HttpServletResponse response) {
        try {
            //所有一级
            List<String> mapOneList = new ArrayList<String>();
            mapOneList.add("陕西省");
            mapOneList.add("湖北省");
            mapOneList.add("安徽省");

            //一二级关系map
            Map<String, List<String>> map = new HashMap<String, List<String>>();
            map.put("陕西省", Arrays.asList("西安市", "渭南市", "咸阳市", "宝鸡市", "商洛市", "安康市"));
            map.put("西安市", Arrays.asList("雁塔区", "碑林区", "长安区", "临潼区", "莲湖区", "未央区"));
            map.put("雁塔区", Arrays.asList("丈八街道", "鱼化寨街道", "小寨路街道", "大雁塔街道", "长延堡街道", "电子城街道"));

            map.put("湖北省", Arrays.asList("武汉市", "荆州市"));
            map.put("安徽省", Arrays.asList("合肥市", "芜湖市"));
            map.put("合肥市", Arrays.asList("肥东县", "肥西县"));
            map.put("肥东县", Arrays.asList("西山镇", "店埠"));
            map.put("西山镇", Arrays.asList("西山镇123", "店埠123"));

            // 模版文件：starsky-excel/src/main/resource/template/cascadeExcel.xlsx
            //第2行第3列插入5级联动
            String filePath = "d:\\测试级联.xlsx";
            Workbook workbook = ExcelUtils.createCascadeExcel(filePath, mapOneList, map, 2, 3, 5);

            String fileName = "excel多级级联-" + DateUtils.format(new Date(), DateUtils.YYYYMMDDHHMMSS);
            ExcelUtils.writeResponse(response, workbook, fileName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

}


